package com.ddw.meedeeuh.tests.shared.movie.database;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertNull;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;

import java.io.File;
import java.io.IOException;
import java.util.Map;

import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Rule;
import org.junit.Test;
import org.junit.rules.TemporaryFolder;

import com.ddw.meedeeuh.shared.database.DatabaseCallback;
import com.ddw.meedeeuh.shared.io.PathUtils;
import com.ddw.meedeeuh.shared.movie.database.DBMovie;
import com.ddw.meedeeuh.shared.movie.database.MovieDB;
import com.ddw.meedeeuh.shared.movie.database.MovieDBFactory;
import com.ddw.meedeeuh.tests.AsyncResult;

public class MovieDBSQLiteTest {

	@Rule
    public static TemporaryFolder folder = null;
	private static MovieDB db = null;

	@BeforeClass
	public static void setUpClass() throws InterruptedException {
		// Create db file and database
		folder = new TemporaryFolder();
		final File file = new File(folder.getRoot(), "movie.db");
		db = MovieDBFactory.create(file);

		// Create database tables/index's
		final AsyncResult<Boolean> result = new AsyncResult<Boolean>();
		db.create(new DatabaseCallback<Boolean>() {

			@Override
			public void onSuccess(Boolean res) {
				result.setResult(res);
			}

			@Override
			public void onFailure(Throwable e) {
				result.setException(e);
			}

		});

		result.await(2000);
		assertTrue(result.hasBeenSet());
		assertTrue(result.getResult());
	}

	@Before
	public void setUp() throws Exception {
		final StringBuilder builder = new StringBuilder();

		// Delete all data
		builder.append("DELETE FROM actor;DELETE FROM video;DELETE FROM audio;DELETE FROM subtitle;DELETE FROM streamdetails;DELETE FROM movie;");

		// Reset all data
		builder.append("INSERT INTO movie (movieid, path, title, originaltitle, sorttitle, movieset, rating, year, top250, " +
				"votes, outline, plot, tagline, runtime, thumbnail, mpaa, playcount, watched, imdbid, trailer, " +
				"genre, credits, director) VALUES (1, 'c:\\movies\\Fight Club (1999)\\Fight Club (1999).dvd.mkv', " +
				"'Fight Club', NULL, NULL, NULL, 8.9, 1999, 1, 608701, " +
				"'An insomniac office worker and a devil-may-care soap maker form an underground fight club that transforms into a violent revolution.', " +
				"'A ticking-time-bomb insomniac and a slippery soap salesman channel primal male aggression into a shocking new form of therapy. " +
				"Their concept catches on, with underground \"fight clubs\" forming in every town, until an eccentric gets in the way and ignites an out-of-control spiral toward oblivion.', " +
				"NULL, 139, NULL, '18', NULL, NULL, 'tt0137523', NULL, 'Drama', 'Brad Pitt, Edward Norton and Helena Bonham Carter', 'David Fincher');");
		builder.append("INSERT INTO actor (actorid, movieid, name, role) VALUES (1, 1, 'Brad Pitt', 'Tyler Durden');");
		builder.append("INSERT INTO actor (actorid, movieid, name, role) VALUES (2, 1, 'Edward Norton', 'The Narrator');");
		builder.append("INSERT INTO actor (actorid, movieid, name, role) VALUES (3, 1, 'Helena Bonham Carter', 'Marla Singer');");
		builder.append("INSERT INTO streamdetails (streamdetailsid, movieid) VALUES (1, 1);");
		builder.append("INSERT INTO video (videoid, streamdetailsid, aspect, codec, width, height) VALUES (1, 1, 1.778, 'h264', 1280, 720);");
		builder.append("INSERT INTO audio (audioid, streamdetailsid, codec, channels, language) VALUES (1, 1, 'ac3', 6, 'eng');");
		builder.append("INSERT INTO audio (audioid, streamdetailsid, codec, channels, language) VALUES (2, 1, 'ac3', 2, 'eng');");
		builder.append("INSERT INTO subtitle (subtitleid, streamdetailsid, language) VALUES (1, 1, 'eng');");
		builder.append("INSERT INTO subtitle (subtitleid, streamdetailsid, language) VALUES (2, 1, 'spa');");

		builder.append("INSERT INTO movie (movieid, path, title, originaltitle, sorttitle, movieset, rating, year, top250, " +
				"votes, outline, plot, tagline, runtime, thumbnail, mpaa, playcount, watched, imdbid, trailer, " +
				"genre, credits, director) VALUES (2, 'c:\\movies\\Hellboy (2004)\\Hellboy (2004).bluray.mkv', " +
				"'Hellboy', NULL, NULL, NULL, 6.9, 2004, 0, 124296, " +
				"'A demon, raised from infancy after being conjured by and rescued from the Nazis, grows up to become a defender against the forces of darkness.', " +
				"'In the final days of World War II, the Nazis attempt to use black magic to aid their dying cause. The Allies raid the camp where the ceremony " +
				"is taking place, but not before a demon - Hellboy - has already been conjured. Joining the Allied forces, " +
				"Hellboy eventually grows to adulthood, serving the cause of good rather than evil', " +
				"'Give Evil Hell', 122, NULL, '12A', 2, 1, 'tt0167190', NULL, 'Action,Adventure,Fantasy', 'Ron Perlman, Doug Jones and Selma Blair', 'Guillermo del Toro');");
		builder.append("INSERT INTO actor (actorid, movieid, name, role) VALUES (4, 2, 'Ron Perlman', 'Hellboy');");
		builder.append("INSERT INTO actor (actorid, movieid, name, role) VALUES (5, 2, 'Doug Jones', 'Abe Sapien');");
		builder.append("INSERT INTO actor (actorid, movieid, name, role) VALUES (6, 2, 'Selma Blair', 'Liz Sherman');");
		builder.append("INSERT INTO streamdetails (streamdetailsid, movieid) VALUES (2, 2);");
		builder.append("INSERT INTO video (videoid, streamdetailsid, aspect, codec, width, height) VALUES (2, 2, 2.35, 'h264', 1920, 816);");
		builder.append("INSERT INTO audio (audioid, streamdetailsid, codec, channels, language) VALUES (3, 2, 'dts', 9, 'eng');");
		builder.append("INSERT INTO audio (audioid, streamdetailsid, codec, channels, language) VALUES (4, 2, 'ac3', 6, 'eng');");
		builder.append("INSERT INTO audio (audioid, streamdetailsid, codec, channels, language) VALUES (5, 2, 'ac3', 2, 'eng');");
		builder.append("INSERT INTO subtitle (subtitleid, streamdetailsid, language) VALUES (3, 2, 'eng');");
		db.exec(builder.toString());
	}

	@Test
	public void testMovieDBSQLite() {
		boolean threwException;
		File file;
		MovieDB instance;

		// Create valid database that doesn't exist
		file = new File("movie.db2");
		instance = MovieDBFactory.create(file);
		assertNotNull(instance);

		// Create valid database that does exist
		try {
			file = folder.newFile("movie.db2");
		} catch (IOException e) {
			fail(e.getMessage());
		}
		instance = MovieDBFactory.create(file);
		assertNotNull(instance);

		// Create invalid database
		file = null;
		threwException = false;
		instance = null;
		try {
			instance = MovieDBFactory.create(file);
		} catch (IllegalArgumentException e) {
			threwException = true;
		}
		assertTrue(threwException);
		assertNull(instance);
	}

	@Test
	public void testMovies() throws Throwable {
		final AsyncResult<Map<Integer, DBMovie>> result = new AsyncResult<Map<Integer, DBMovie>>();
		DBMovie movie;

		db.movies(new DatabaseCallback<Map<Integer, DBMovie>>() {

			@Override
			public void onSuccess(Map<Integer, DBMovie> res) {
				result.setResult(res);
			}

			@Override
			public void onFailure(Throwable e) {
				result.setException(e);
			}

		});

		result.await(500);

		// Test result was set and no exception
		assertTrue(result.hasBeenSet());
		if(result.getException() != null) {
			throw result.getException();
		}

		// Test size of results
		assertEquals(2, result.getResult().size());

		// Test fight club
		assertTrue(result.getResult().containsKey(1));
		movie = result.getResult().get(1);
		checkIsFightClub(movie);

		// Test hellboy
		assertTrue(result.getResult().containsKey(2));
		movie = result.getResult().get(2);
		checkIsHellboy(movie);
	}

	@Test
	public void testMovie() throws Throwable {
		final AsyncResult<DBMovie> result = new AsyncResult<DBMovie>();
		int movieId;
		DBMovie movie;

		// Load a valid movie
		result.reset();
		movieId = 2;
		db.movie(movieId, new DatabaseCallback<DBMovie>() {

			@Override
			public void onSuccess(DBMovie res) {
				result.setResult(res);
			}

			@Override
			public void onFailure(Throwable e) {
				result.setException(e);
			}

		});

		result.await(2000);

		// Test result was set and no exception
		assertTrue(result.hasBeenSet());
		if(result.getException() != null) {
			throw result.getException();
		}

		// Test hellboy
		movie = result.getResult();
		checkIsHellboy(movie);

		// Load an invalid movie
		result.reset();
		movieId = -1;
		db.movie(movieId, new DatabaseCallback<DBMovie>() {

			@Override
			public void onSuccess(DBMovie res) {
				result.setResult(res);
			}

			@Override
			public void onFailure(Throwable e) {
				result.setException(e);
			}

		});

		result.await(2000);

		// Test result was set and exception was thrown
		assertTrue(result.hasBeenSet());
		assertNotNull(result.getException());
	}

	@Test
	public void testDeleteMovies() throws Throwable {
		final AsyncResult<Boolean> result = new AsyncResult<Boolean>();
		int movieCount;

		// Check number of movies
		movieCount = db.execInt("SELECT COUNT(*) FROM movie");
		assertEquals(2, movieCount);

		// Delete the movies
		db.deleteMovies(new DatabaseCallback<Boolean>() {

			@Override
			public void onSuccess(Boolean res) {
				result.setResult(res);
			}

			@Override
			public void onFailure(Throwable e) {
				result.setException(e);
			}

		});

		result.await(2000);

		// Check the delete happened and was successful
		assertTrue(result.hasBeenSet());
		if(result.getException() != null) {
			throw result.getException();
		}
		assertTrue(result.getResult());

		// Count the total number of movies again
		movieCount = db.execInt("SELECT COUNT(*) FROM movie");
		assertEquals(0, movieCount);
	}

	@Test
	public void testDeleteMovie() throws Throwable {
		final AsyncResult<Boolean> result = new AsyncResult<Boolean>();
		int movieCount;
		int movieId;

		// Check number of movies
		movieCount = db.execInt("SELECT COUNT(*) FROM movie");
		assertEquals(2, movieCount);

		// Delete the movies
		movieId = 2;
		db.deleteMovie(movieId, new DatabaseCallback<Boolean>() {

			@Override
			public void onSuccess(Boolean res) {
				result.setResult(res);
			}

			@Override
			public void onFailure(Throwable e) {
				result.setException(e);
			}

		});

		result.await(2000);

		// Check the delete happened and was successful
		assertTrue(result.hasBeenSet());
		if(result.getException() != null) {
			throw result.getException();
		}
		assertTrue(result.getResult());

		// Count the total number of movies again
		movieCount = db.execInt("SELECT COUNT(*) FROM movie");
		assertEquals(1, movieCount);

		// Check it deleted the correct movie
		movieCount = db.execInt("SELECT COUNT(*) FROM movie WHERE movieid = 1");
		assertEquals(1, movieCount);
		movieCount = db.execInt("SELECT COUNT(*) FROM movie WHERE movieid = 2");
		assertEquals(0, movieCount);
	}

	@Test
	public void testSetMovie() {
		fail("Not yet implemented");
	}

	@Test
	public void testCreate() throws Throwable {
		final File file = new File(PathUtils.combine(folder.getRoot(), "testCreate.db"));
		final MovieDB db = MovieDBFactory.create(file);
		final AsyncResult<Boolean> result = new AsyncResult<Boolean>();

		// Test creating database on empty file
		db.create(new DatabaseCallback<Boolean>() {

			@Override
			public void onSuccess(Boolean res) {
				result.setResult(res);
			}

			@Override
			public void onFailure(Throwable e) {
				result.setException(e);
			}

		});

		result.await(2000);

		// Check the database was created successfully
		assertTrue(result.hasBeenSet());
		if(result.getException() != null) {
			throw result.getException();
		}
		assertTrue(result.getResult());

		// Try to create the database again
		result.reset();
		db.create(new DatabaseCallback<Boolean>() {

			@Override
			public void onSuccess(Boolean res) {
				result.setResult(res);
			}

			@Override
			public void onFailure(Throwable e) {
				result.setException(e);
			}

		});

		result.await(2000);

		// Check the database failed to create
		assertTrue(result.hasBeenSet());
		assertNotNull(result.getException());
		assertNull(result.getResult());
	}

	@Test
	public void testExec() {
		String sql;
		boolean failed;

		// Test simple query
		sql = "SELECT 1 + 1";
		failed = false;
		try {
			db.exec(sql);
		} catch (Throwable e) {
			failed = true;
		}
		assertTrue(failed == false);

		// Test invalid query
		sql = "THIS IS NOT SQL";
		failed = false;
		try {
			db.exec(sql);
		} catch (Throwable e) {
			failed = true;
		}
		assertTrue(failed);
	}

	@Test
	public void testExecInt() {
		String sql;
		int result;
		boolean failed;

		// Test simple query
		sql = "SELECT 1 + 1";
		result = -1;
		failed = false;
		try {
			result = db.execInt(sql);
		} catch (Throwable e) {
			failed = true;
		}
		assertTrue(failed == false);
		assertEquals(2, result);

		// Test invalid query
		sql = "THIS IS NOT SQL";
		result = -1;
		failed = false;
		try {
			result = db.execInt(sql);
		} catch (Throwable e) {
			failed = true;
		}
		assertTrue(failed);
		assertEquals(-1, result);
	}

	@Test
	public void testExecDouble() {
		String sql;
		double result;
		boolean failed;

		// Test simple query
		sql = "SELECT 1.0 + 1.0";
		result = -1.0;
		failed = false;
		try {
			result = db.execDouble(sql);
		} catch (Throwable e) {
			failed = true;
		}
		assertTrue(failed == false);
		assertEquals(2.0, result, 0.00001);

		// Test invalid query
		sql = "THIS IS NOT SQL";
		result = -1.0;
		failed = false;
		try {
			result = db.execDouble(sql);
		} catch (Throwable e) {
			failed = true;
		}
		assertTrue(failed);
		assertEquals(-1.0, result, 0.00001);
	}

	@Test
	public void testExecString() {
		String sql;
		String result;
		boolean failed;

		// Test simple query
		sql = "SELECT 'hello'";
		result = null;
		failed = false;
		try {
			result = db.execString(sql);
		} catch (Throwable e) {
			failed = true;
		}
		assertTrue(failed == false);
		assertEquals("hello", result);

		// Test invalid query
		sql = "THIS IS NOT SQL";
		result = null;
		failed = false;
		try {
			result = db.execString(sql);
		} catch (Throwable e) {
			failed = true;
		}
		assertTrue(failed);
		assertEquals(null, result);
	}

	@Test
	public void testExecBoolean() {
		String sql;
		boolean result;
		boolean failed;

		// Test simple query
		sql = "SELECT 1";
		result = false;
		failed = false;
		try {
			result = db.execBoolean(sql);
		} catch (Throwable e) {
			failed = true;
		}
		assertTrue(failed == false);
		assertTrue(result);

		// Test simple query
		sql = "SELECT 0";
		result = false;
		failed = false;
		try {
			result = db.execBoolean(sql);
		} catch (Throwable e) {
			failed = true;
		}
		assertTrue(failed == false);
		assertTrue(result == false);

		// Test invalid query
		sql = "THIS IS NOT SQL";
		result = false;
		failed = false;
		try {
			result = db.execBoolean(sql);
		} catch (Throwable e) {
			failed = true;
		}
		assertTrue(failed);
		assertEquals(false, result);
	}

	private void checkIsHellboy(DBMovie movie) {
		assertNotNull(movie);
		assertEquals(2, movie.getMovieId());
		assertEquals("Ron Perlman, Doug Jones and Selma Blair", movie.getCredits());
		assertEquals("Guillermo del Toro", movie.getDirector());
		assertEquals("Action,Adventure,Fantasy", movie.getGenre());
		assertEquals("tt0167190", movie.getImdbId());
		assertEquals("12A", movie.getMpaa());
		assertEquals("Hellboy", movie.getName());
		assertEquals(null, movie.getOriginalTitle());
		assertEquals("A demon, raised from infancy after being conjured by and rescued from the Nazis, grows up to become a defender against the forces of darkness.", movie.getOutline());
		assertEquals(new Integer(2), movie.getPlayCount());
		assertEquals("In the final days of World War II, the Nazis attempt to use black magic to aid their dying cause. The Allies raid the camp where the ceremony " +
				"is taking place, but not before a demon - Hellboy - has already been conjured. Joining the Allied forces, " +
				"Hellboy eventually grows to adulthood, serving the cause of good rather than evil", movie.getPlot());
		assertEquals(new Double(6.9), movie.getRating());
		assertEquals(new Integer(122), movie.getRuntime());
		assertEquals(null, movie.getSet());
		assertEquals(null, movie.getSortTitle());
		assertEquals("Give Evil Hell", movie.getTagLine());
		assertEquals(null, movie.getThumbnail());
		assertEquals(null, movie.getTrailer());
		assertEquals(new Integer(124296), movie.getVotes());
		assertEquals(new Integer(2004), movie.getYear());
	}

	private void checkIsFightClub(DBMovie movie) {
		assertNotNull(movie);
		assertEquals(1, movie.getMovieId());
		assertEquals("Brad Pitt, Edward Norton and Helena Bonham Carter", movie.getCredits());
		assertEquals("David Fincher", movie.getDirector());
		assertEquals("Drama", movie.getGenre());
		assertEquals("tt0137523", movie.getImdbId());
		assertEquals("18", movie.getMpaa());
		assertEquals("Fight Club", movie.getName());
		assertEquals(null, movie.getOriginalTitle());
		assertEquals("An insomniac office worker and a devil-may-care soap maker form an underground fight club that transforms into a violent revolution.", movie.getOutline());
		assertEquals(null, movie.getPlayCount());
		assertEquals("A ticking-time-bomb insomniac and a slippery soap salesman channel primal " +
				"male aggression into a shocking new form of therapy. Their concept catches on, " +
				"with underground \"fight clubs\" forming in every town, until an eccentric gets " +
				"in the way and ignites an out-of-control spiral toward oblivion.", movie.getPlot());
		assertEquals(new Double(8.9), movie.getRating());
		assertEquals(new Integer(139), movie.getRuntime());
		assertEquals(null, movie.getSet());
		assertEquals(null, movie.getSortTitle());
		assertEquals(null, movie.getTagLine());
		assertEquals(null, movie.getThumbnail());
		assertEquals(null, movie.getTrailer());
		assertEquals(new Integer(608701), movie.getVotes());
		assertEquals(new Integer(1999), movie.getYear());
	}

}
